Configuring Read-Only Routing

Introduction

Read-only routing refers to the ability of SQL Server to route incoming connections to an availability group listener to a secondary replica that is configured to allow read-only workloads. During this lab, you will learn how to configure and use read-only routing to route read-intent listener connections to the secondary replica.

Objectives

At the end of this lab, you will be able to:

  • Configure Read-Only Routing
  • Test Read-Only Routing

Estimated Time

20 minutes

Logon Information

Before Login make sure windows has Applied Computer Setting to all nodes.

  1. Connect to AlwaysOnClient as CorpNet\Cluadmin using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  2. Connect to AlwaysOnN1 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  3. Connect to AlwaysOnN2 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  4. Connect to AlwaysOnN3 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  5. Connect to AlwaysOnDC as Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  6. Change the screen resolution if required.

    You may want to adjust the screen resolution to your own preference. Do this by right-clicking on the desktop and choosing Screen resolution and clicking OK when finished.

Exercise 1: Configure Read-Only Routing

This exercise shows how to configure Read-Only routing for an availability group.

Tasks

  1. Open SQL Server Management Studio (SSMS)

    1. Log into the AlwaysOnClient server as Corpnet\cluadmin using Pa$$w0rd as the password.

      Click the Type Text icon to enter the associated text into the virtual machine.

    2. Open SSMS by left clicking the icon on the taskbar.

  2. Connect to the availability group listener

    1. In the Connect to Server dialog box, type in AGCorpListen and then click Connect.

  3. Open the Read-Only Routing Scripts and investigate the syntax

    1. In SSMS, click File > Open > File… or press Control+O

    2. Navigate to C:\Scripts and open 09_Read_Only_Routing_Information.sql and 09_Read_Only_Routing_Config.sql

    3. Execute the 09_Read_Only_Routing_Information T-SQL.

      The results should be NULL column values for the secondary_route_reader_server and routing_priority as read-only routing has not been setup.

    4. Review the 09_Read_Only_Routing_Config T-SQL file.

      Notice that there is a new syntax which allows the use of nested parenthesis such as: (('AlwaysOnN1', 'AlwaysOnN2'), 'AlwaysOnN3'). This is the new syntax for round-robin load balancing between secondary replicas. The inner parenthesis represents the load balance group and the outer represent the entire read-only routing list.

    5. Execute the 09_Read_Only_Routing_Config script in its entirety. This script will define a read-only routing URL address for each replica and specify a read-only routing list for each availability replica while in the primary role.

      If you have SQL Server Management Studio 17.3 or above, you can configure the read-only routing URL and the read-only routing list via the Availability Groups Properties UI. If your SSMS build is less than 17.3 then you can configure them using T-SQL or PowerShell.

    6. Execute the 09_Read_Only_Routing_Information script and view the new results.

      Notice that the routing priority for the round-robin load balancing pairs are both set to 1.

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next exercise.

Exercise 2: Test Read-Only Routing

This exercise shows how to test and check that read-only routing is working properly with readable secondary replicas and a configured read-only routing list and URL.

Tasks

  1. Open Command Prompt window

    1. Log into the AlwaysOnClient server as Corpnet\cluadmin using Pa$$w0rd as the password.

    2. Open a Command Prompt window.

  2. Test the read-only routing connection

    1. Test your newly configured read-only routing using SQLCMD by specifying the application intent option (-K) along with the listener name and database name. After connection is made, check the servername.

    Open Notepad in the lab environment VM. Use the Type Text feature to enter the code into Notepad, copy the code, and then paste it into the command prompt.

    Sample Commands

    SQLCMD
    SQLCMD –S AGCorpListen –E -d AdventureWorks -K ReadOnly
SELECT @@SERVERNAME
GO

  3. What is the server name that was returned? Is this one of the expected secondary replicas?

  4. Open another command prompt window and repeat steps 2 and 3. What is the server name that was returned? Is this expected?

Congratulations!

You have successfully completed this exercise. You can move to the next lab.